﻿using System;
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


/// <summary>
/// SqlDBA 的摘要说明。
/// </summary>
public class SqlDBA
{
    //public static string strConnection;

    public SqlDBA()
    {
        //SqlDBA.strConnection = ConfigurationManager.AppSettings["ConnString"];
    }

    /// <summary>
    /// 运行存储过程
    /// </summary>
    /// <param name="procName">存储过程名称.</param>
    /// <param name="prams">存储过程参数.</param>
    /// <returns>返回值.</returns>
    public static int RunProc(SqlConnection conn, string procName, SqlParameter[] prams)
    {
        SqlCommand cmd = CreateCommand(conn, procName, prams);
        cmd.ExecuteNonQuery();
        //			Close();
        //			Dispose();
        return (int)cmd.Parameters["ReturnValue"].Value;
    }

    /// <summary>
    /// 运行存储过程
    /// </summary>
    /// <param name="procName">存储过程名称.</param>
    /// <param name="dataReader">返回值.</param>
    public static void RunProc(SqlConnection conn, string procName, out SqlDataReader dataReader)
    {
        SqlCommand cmd = CreateCommand(conn, procName, null);
        dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
    }

    /// <summary>
    /// 运行存储过程
    /// </summary>
    /// <param name="procName">存储过程名称.</param>
    /// <param name="prams">存储过程参数.</param>
    /// <param name="dataReader">返回值.</param>
    public static void RunProc(SqlConnection conn, string procName, SqlParameter[] prams, out SqlDataReader dataReader)
    {
        SqlCommand cmd = CreateCommand(conn, procName, prams);
        dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
    }

    public static void RunProc(SqlConnection conn, string procName, SqlParameter[] prams, out DataSet dataReader)
    {
        SqlCommand cmd = CreateCommand(conn, procName, prams);
        using (SqlDataAdapter adapter1 = new SqlDataAdapter(cmd))
        {
            DataSet set1 = new DataSet();
            adapter1.Fill(set1);
            cmd.Parameters.Clear();
            conn.Close();
            conn.Dispose();
            dataReader = set1;
            adapter1.Dispose();
        }
    }
    /// <summary>
    /// 创建命令对象，调用存储过程
    /// </summary>
    /// <param name="procName">存储过程名称.</param>
    /// <param name="prams">存储过程参数.</param>
    /// <returns>返回命令对象.</returns>
    private static SqlCommand CreateCommand(SqlConnection conn, string procName, SqlParameter[] prams)
    {
        // 确保连接是打开的
        //Open();

        SqlCommand cmd = new SqlCommand(procName, conn);
        cmd.CommandType = CommandType.StoredProcedure;

        // 给存储过程添加参数
        if (prams != null)
        {
            foreach (SqlParameter parameter in prams)
                cmd.Parameters.Add(parameter);
        }

        // 返回参数
        cmd.Parameters.Add(
            new SqlParameter("ReturnValue", SqlDbType.Int, 4,
            ParameterDirection.ReturnValue, false, 0, 0,
            string.Empty, DataRowVersion.Default, null));

        return cmd;
    }
    public static SqlCommand CreateCommandSql(SqlConnection conn, string procName, SqlParameter[] prams)
    {
        // 确保连接是打开的
        //Open();

        SqlCommand cmd = new SqlCommand(procName, conn);
        cmd.CommandType = CommandType.Text;
        cmd.CommandTimeout = 180;

        // 给存储过程添加参数
        if (prams != null)
        {
            foreach (SqlParameter parameter in prams)
                cmd.Parameters.Add(parameter);
        }

        // 返回参数
        cmd.Parameters.Add(
            new SqlParameter("ReturnValue", SqlDbType.Int, 4,
            ParameterDirection.ReturnValue, false, 0, 0,
            string.Empty, DataRowVersion.Default, null));

        return cmd;
    }
    /// <summary>
    /// 生成输入参数
    /// </summary>
    /// <param name="ParamName">参数名称.</param>
    /// <param name="DbType">参数类型.</param>
    /// <param name="Size">参数大小.</param>
    /// <param name="Value">参数值.</param>
    /// <returns>返回新参数.</returns>
    public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
    {
        return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
    }

    /// <summary>
    /// 生成输出参数.
    /// </summary>
    /// <param name="ParamName">参数名称.</param>
    /// <param name="DbType">参数类型.</param>
    /// <param name="Size">参数大小.</param>
    /// <returns>返回新参数.</returns>
    public static SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
    {
        return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
    }

    /// <summary>
    /// 生成存储过程参数
    /// </summary>
    /// <param name="ParamName">参数名称.</param>
    /// <param name="DbType">参数类型.</param>
    /// <param name="Size">参数大小.</param>
    /// <param name="Direction">参数方向.</param>
    /// <param name="Value">参数值.</param>
    /// <returns>返回新参数.</returns>
    public static SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
    {
        SqlParameter param;

        if (Size > 0)
            param = new SqlParameter(ParamName, DbType, Size);
        else
            param = new SqlParameter(ParamName, DbType);

        param.Direction = Direction;
        if (!(Direction == ParameterDirection.Output && Value == null))
            param.Value = Value;

        return param;
    }
}

